﻿CREATE FUNCTION PaymentSchedule_DCC(
	@LoanAmount numeric(38,10),
	@Payment numeric(38,10),
	@ContractDate datetime,
	@FirstPaymentDate datetime,
	@NumberOfPayments int,
	@PaymentsPerYear numeric(38,10),
	@InterestRate numeric(38,10),
	@DayCountConvention tinyint,
	@PaymentStart int = 0,
	@PaymentEnd int = 0
) RETURNS @AmrtTbl TABLE(PaymentNumber int IDENTITY,Mos int,Date datetime,Days smallint,
		ToPrinciple numeric(18,2),ToInterest numeric(18,2),	RemainingPrinciple numeric(18,2),FinanceCharge numeric(18,2),
		MinBalanceToRoll numeric(18,2),MinBalanceRatio int) AS
BEGIN
DECLARE @DateStart datetime,@DateEnd datetime,@FinanceCharge numeric(38,2),@PmtNum int,@AccrInt numeric(10,2)
SELECT @DateStart = @ContractDate,@DateEnd = @FirstPaymentDate,@FinanceCharge = 0,@PmtNum = 1
IF @InterestRate > 1 SET @InterestRate = @InterestRate / 100.0000000000
DECLARE @PerDiem numeric(10,10)

IF ISNULL(@PaymentStart,0) <= 0 SET @PaymentStart = 1
IF ISNULL(@PaymentEnd,0) <= 0 SET @PaymentEnd = @NumberOfPayments

WHILE @PmtNum <= @NumberOfPayments BEGIN
	--SET @PerDiem = @InterestRate/dbo.BaseDays(@DateEnd,@DayCountConvention)
	--SET @PerDiem = @InterestRate/365
	--SET @AccrInt = dbo.MaxNum(DATEDIFF(dd,@DateStart,@DateEnd),0)*@PerDiem*@LoanAmount
	SET @AccrInt = dbo.ACCRINTM(@DateStart,@DateEnd,@InterestRate,@LoanAmount,@DayCountConvention)
	--SET @AccrInt = CEILING(wct.ACCRINTM(@DateStart,@DateEnd,@InterestRate,@LoanAmount,@DayCountConvention)*100.074)/100
	SELECT @FinanceCharge = @FinanceCharge + @AccrInt,@LoanAmount = @LoanAmount - @Payment + @AccrInt
	IF @PmtNum >= @PaymentStart AND @PmtNum <= @PaymentEnd
		INSERT INTO @AmrtTbl VALUES(@PmtNum/@PaymentsPerYear*12,@DateEnd,DATEDIFF(dd,@DateStart,@DateEnd),
		@Payment - @AccrInt,@AccrInt,@LoanAmount,@FinanceCharge,@LoanAmount+(@Payment - @AccrInt)*.35,((@LoanAmount+(@Payment - @AccrInt)*.30)-@LoanAmount)/@Payment*100.0000)
	SET @DateStart = @DateEnd
	SET @DateEnd = dbo.NextPaymentDate(@DateStart,@PaymentsPerYear,1)
	SET @PmtNum = @PmtNum + 1
END

RETURN
END






